/*

	Description	:	This Procedure calculates the Individual Cost at each process in Chiptrac System.
	Usage		:	exec sp_new_performance_cost_report 0,0,'detail','0','0','0'
				exec sp_new_performance_cost_report 0,0,'Summary','0','0','0'
	Note		:	Need to execute the following before using this procedure.
				* exec sp_load_all_process_yield_cost_details(This procedure dumps all the Yield records 
				from all processes in chiptrac system to 'Yield_Lot_Table' table.)
				* exec sp_update_cost_table(This procedure dumps the standard cost of current quarter
				from update view screen into 'cost_table'.)
Tables Used in this procedure.
----------------------------	
select * from Yield_Lot_Table
select * from cost_table
select * from Transportation_Table
	Need to  as ps_type_id and ps_type_id should be same for all prcoess whose part number is same so that we can get in a single line.

*/
ALTER proc sp_new_performance_cost_report

@baseproduct_id	 int,
@processstep_id	 int,
@category_type 	 varchar(50),
@date_from 		 varchar(50),
@date_to 		 varchar(50),
@default_prompt 	 varchar(50)

as
begin

create table #results(		LotNumber	varchar(50),      BaseProduct varchar(50),part_number varchar(50),
				Freight 		numeric(20,4),	Backgrind 		numeric(20,4),
				FabQtyOut	int, 		FabCost 		numeric(20,4), 
				SortQtyOut 	int, 		SortCost 		numeric(20,4),
				DiebankQtyOut 	int, 		DiebankCost 		numeric(20,4),
				AssmQtyOut 	int, 		AssmCost 		numeric(20,4),
				TestQtyOut 	int, 		TestCost 		numeric(20,4),
				MPQtyOut 	int, 		MPCost 			numeric(20,4),
				FGQtyOut 	int, 		FGCost 			numeric(20,4),
				p_type 		int)--		ps_type 		int )

declare @QUARTER int
declare @sqlstring	nvarchar(4000)
declare @sqlstring1	nvarchar(4000)
declare @sqlstring2	nvarchar(2000)

SET @QUARTER = DATEPART( Q, GETDATE() )

	--Fab Information(here we are multiplying fab qty out and std cost per die. and this process is repeated for all process.)
	SET @sqlstring = N'insert into #results
	SELECT LotNumber,BaseProduct,(select part_number from product where processstep_id = Fab_Processstep_id) as PartNumber,
		null as Freight,null as Backgrind,
		FabQtyOut,(FabQtyOut*GrossDie)* isnull( (select 
						case 
							when '+ convert(varchar,@QUARTER) + ' = 1 then Q1_STD_COST_PER_DIE 
							when '+ convert(varchar,@QUARTER) + ' = 2 then Q2_STD_COST_PER_DIE 
							when '+ convert(varchar,@QUARTER) + ' = 3 then Q3_STD_COST_PER_DIE 
							when '+ convert(varchar,@QUARTER) + ' = 4 then Q4_STD_COST_PER_DIE 
						end
				 		from cost_table where processstep_id = Fab_Processstep_id and location_id =FabLocation) ,0.00) ,
		null,null,		--Var for sort
		null,null,		--Var for Die bank
		null,null,		--Var for Assembly
		null,null,		--Var for Test
		null,null,		--Var for Mark and Pack
		null,null,		--Var for Finishedgoods
		isnull(p_type,1)	-- for product_type
	FROM Yield_Lot_Table where FabQtyIn > 0'
	
	if @baseproduct_id <> 0
	begin
		set @sqlstring = @sqlstring + ' and baseproduct_id = '+ convert(varchar,@baseproduct_id)
	end
	if @processstep_id <> 0
	begin
		set @sqlstring = @sqlstring + ' and fab_processstep_id = '+ convert(varchar,@processstep_id)
	end
	if @date_from is not null and ltrim(rtrim(@date_from)) <> ''  and  @date_from <> '0'
	begin
		set @sqlstring = @sqlstring + ' and FabDateOut >= ''' + convert(varchar, convert(datetime,@date_from) ) + ''''
	end
	if @date_to is not null and ltrim(rtrim(@date_to)) <> ''  and  @date_to <> '0'
	begin
		set @sqlstring = @sqlstring + ' and FabDateOut  >= ''' + convert(varchar, convert(datetime,@date_to) )  + ''''
	end
	

	set @sqlstring = @sqlstring + ' UNION '	
	--Sort Information
	set @sqlstring = @sqlstring + N'
	SELECT LotNumber,BaseProduct,(select part_number from product where processstep_id = Sort_Processstep_id) as PartNumber,
		isnull ( (select tt_normal_cost from Transportation_Table where processstep_id = Fab_Processstep_id and tt_source_location_id = FabLocation and tt_destination_location_id = SortLocation ) , 0.00) as Freight,null as Backgrind,
		null,null,		--Var for Fab
		SortQtyOut,(SortQtyOut*GrossDie)*isnull( (select 
						case 
							when '+ convert(varchar,@QUARTER) + ' = 1 then Q1_STD_COST_PER_DIE 
							when '+ convert(varchar,@QUARTER) + ' = 2 then Q2_STD_COST_PER_DIE 
							when '+ convert(varchar,@QUARTER) + ' = 3 then Q3_STD_COST_PER_DIE 
							when '+ convert(varchar,@QUARTER) + ' = 4 then Q4_STD_COST_PER_DIE 
						end
						from cost_table where processstep_id = Sort_Processstep_id and location_id =SortLocation) ,0.00) ,
		null,null,		--Var for Die bank
		null,null,		--Var for Assembly
		null,null,		--Var for Test
		null,null,		--Var for Mark and Pack
		null,null,		--Var for Finishedgoods
		isnull(p_type,1)	-- for product_type
	 FROM Yield_Lot_Table where SortQtyIn > 0'

	if @baseproduct_id <> 0
	begin
		set @sqlstring = @sqlstring + ' and baseproduct_id ='+ convert(varchar,@baseproduct_id)
	end
	 if @processstep_id <> 0
	 begin
		set @sqlstring = @sqlstring + ' and Sort_Processstep_id = '+ convert(varchar,@processstep_id)
	 end
	 if @date_from is not null and ltrim(rtrim(@date_from)) <> ''  and  @date_from <> '0'
	 begin
		set @sqlstring = @sqlstring + ' and SortDateOut >=  ''' + convert(varchar, convert(datetime,@date_from) ) + ''''
	 end
	 if @date_to is not null and ltrim(rtrim(@date_to)) <> ''  and  @date_to <> '0'
	 begin
		set @sqlstring = @sqlstring + ' and SortDateOut <= ''' + convert(varchar, convert(datetime,@date_to) )  + ''''
	 end


	set @sqlstring = @sqlstring + ' UNION '	
	--Die bank Information
	set @sqlstring = @sqlstring + N'
	SELECT LotNumber,BaseProduct,(select part_number from product where processstep_id = Diebank_Processstep_id) as PartNumber,
		null as Freight,null as Backgrind,
		null,null,		--Var for Fab
		null,null,		--Var for Sort
		DiebankQtyOut,DiebankQtyOut * isnull( (select 
							case 
								when '+ convert(varchar,@QUARTER) + ' = 1 then Q1_STD_COST_PER_DIE 
								when '+ convert(varchar,@QUARTER) + ' = 2 then Q2_STD_COST_PER_DIE 
								when '+ convert(varchar,@QUARTER) + ' = 3 then Q3_STD_COST_PER_DIE 
								when '+ convert(varchar,@QUARTER) + ' = 4 then Q4_STD_COST_PER_DIE 
							end 
						from cost_table where processstep_id = Diebank_Processstep_id and location_id =DiebankLocation) ,0.00) ,
		null,null,		--Var for Assembly
		null,null,		--Var for Test
		null,null,		--Var for Mark and Pack
		null,null,		--Var for Finishedgoods
		isnull(p_type,1)	-- for product_type
	FROM Yield_Lot_Table where DiebankQtyIn > 0'

	if @baseproduct_id <> 0
	begin
		set @sqlstring = @sqlstring + ' and baseproduct_id ='+ convert(varchar,@baseproduct_id)
	end
	if @processstep_id <> 0
	 begin
		set @sqlstring = @sqlstring + ' and Diebank_Processstep_id = '+ convert(varchar,@processstep_id)
	 end
	 if @date_from is not null and ltrim(rtrim(@date_from)) <> ''  and  @date_from <> '0'
	 begin
		set @sqlstring = @sqlstring + ' and DiebankDateOut >=  ''' + convert(varchar, convert(datetime,@date_from) ) + ''''
	 end
	 if @date_to is not null and ltrim(rtrim(@date_to)) <> ''  and  @date_to <> '0'
	 begin
		set @sqlstring = @sqlstring + ' and DiebankDateOut <= ''' + convert(varchar, convert(datetime,@date_to) )  + ''''
	 end
	

	set @sqlstring = @sqlstring + ' UNION '	
	--Assembly Information
	set @sqlstring = @sqlstring + N'
	SELECT LotNumber,BaseProduct,(select part_number from product where processstep_id = Assm_Processstep_id) as PartNumber,
		null as Freight,null as Backgrind,
		null,null,		--Var for Fab
		null,null,		--Var for Sort
		null,null,		--Var for Die bank
		AssmQtyOut,AssmQtyOut * isnull( (select 
							case 
								when '+ convert(varchar,@QUARTER) + ' = 1 then Q1_STD_COST_PER_DIE 
								when '+ convert(varchar,@QUARTER) + ' = 2 then Q2_STD_COST_PER_DIE 
								when '+ convert(varchar,@QUARTER) + ' = 3 then Q3_STD_COST_PER_DIE 
								when '+ convert(varchar,@QUARTER) + ' = 4 then Q4_STD_COST_PER_DIE 
							end 
							from cost_table where processstep_id = Assm_Processstep_id and location_id =AssmLocation) ,0.00) ,
		null,null,		--Var for Test
		null,null,		--Var for Mark and Pack
		null,null,		--Var for Finishedgoods
		isnull(p_type,1)	-- for product_type
	FROM Yield_Lot_Table where AssmQtyIn > 0'

	if @baseproduct_id <> 0
	begin
		set @sqlstring = @sqlstring + ' and baseproduct_id ='+ convert(varchar,@baseproduct_id)
	end
	if @processstep_id <> 0
	 begin
		set @sqlstring = @sqlstring + ' and Assm_Processstep_id = '+ convert(varchar,@processstep_id)
	 end
	 if @date_from is not null and ltrim(rtrim(@date_from)) <> ''  and  @date_from <> '0'
	 begin
		set @sqlstring = @sqlstring + ' and AssmDateOut >=  ''' + convert(varchar, convert(datetime,@date_from) ) + ''''
	 end
	 if @date_to is not null and ltrim(rtrim(@date_to)) <> ''  and  @date_to <> '0'
	 begin
		set @sqlstring = @sqlstring + ' and AssmDateOut <= ''' + convert(varchar, convert(datetime,@date_to) )  + ''''
	 end
	

	set @sqlstring = @sqlstring  + ' UNION '	
	--Test Information
	set  @sqlstring1 = N'
	SELECT LotNumber,BaseProduct,(select part_number from product where processstep_id = Test_Processstep_id) as PartNumber,
		null as Freight,null as Backgrind,
		null,null,		--Var for Fab
		null,null,		--Var for Sort
		null,null,		--Var for Die bank
		null,null,		--Var for Assembly
		TestQtyOut,TestQtyOut * isnull( (select 
							case 
								when '+ convert(varchar,@QUARTER) + ' = 1 then Q1_STD_COST_PER_DIE 
								when '+ convert(varchar,@QUARTER) + ' = 2 then Q2_STD_COST_PER_DIE 
								when '+ convert(varchar,@QUARTER) + ' = 3 then Q3_STD_COST_PER_DIE 
								when '+ convert(varchar,@QUARTER) + ' = 4 then Q4_STD_COST_PER_DIE  
							end
							from cost_table where processstep_id = Test_Processstep_id and location_id =TestLocation) ,0.00) ,
		null,null,		--Var for Mark and Pack
		null,null, 	--Var for Finishedgoods
		isnull(p_type,1)	-- for product_type
	FROM Yield_Lot_Table where TestQtyIn > 0'

	if @baseproduct_id <> 0
	begin
		set @sqlstring1 = @sqlstring1 + ' and baseproduct_id ='+ convert(varchar,@baseproduct_id)
	end
	if @processstep_id <> 0
	begin
		set @sqlstring1 = @sqlstring1 + ' and Test_Processstep_id = '+ convert(varchar,@processstep_id)
	end
	if @date_from is not null and ltrim(rtrim(@date_from)) <> ''  and  @date_from <> '0'
	begin
		set @sqlstring1 = @sqlstring1 + ' and TestDateOut >=  ''' + convert(varchar, convert(datetime,@date_from) ) + ''''
	end
	if @date_to is not null and ltrim(rtrim(@date_to)) <> ''  and  @date_to <> '0'
	begin
		set @sqlstring1 = @sqlstring1 + ' and TestDateOut <= ''' + convert(varchar, convert(datetime,@date_to) )  + ''''
	end
	

	set @sqlstring1 = @sqlstring1 + ' UNION '	
	--Mark and Pack Information
	set @sqlstring1 = @sqlstring1 + N'
	SELECT LotNumber,BaseProduct,(select part_number from product where processstep_id = MP_Processstep_id) as PartNumber,
		null as Freight,null as Backgrind,
		null,null,		--Var for Fab
		null,null,		--Var for Sort
		null,null,		--Var for Die bank
		null,null,		--Var for Assembly
		null,null,		--Var for Test
		MPQtyOut,MPQtyOut * isnull( (select 
						case 
							when '+ convert(varchar,@QUARTER) + ' = 1 then Q1_STD_COST_PER_DIE 
							when '+ convert(varchar,@QUARTER) + ' = 2 then Q2_STD_COST_PER_DIE 
							when '+ convert(varchar,@QUARTER) + ' = 3 then Q3_STD_COST_PER_DIE 
							when '+ convert(varchar,@QUARTER) + ' = 4 then Q4_STD_COST_PER_DIE 
						end
						from cost_table where processstep_id = MP_Processstep_id and location_id =MPLocation) ,0.00) ,
		null,null,		--Var for Finishedgoods
		isnull(p_type,1)	-- for product_type
	FROM Yield_Lot_Table where MPQtyIn > 0'
	
	if @baseproduct_id <> 0
	begin
		set @sqlstring1 = @sqlstring1 + ' and baseproduct_id ='+ convert(varchar,@baseproduct_id)
	end
	if @processstep_id <> 0
	begin
		set @sqlstring1 = @sqlstring1 + ' and MP_Processstep_id = '+ convert(varchar,@processstep_id)
	end
	if @date_from is not null and ltrim(rtrim(@date_from)) <> ''  and  @date_from <> '0'
	begin
		set @sqlstring1 = @sqlstring1 + ' and MPDateOut >=  ''' + convert(varchar, convert(datetime,@date_from) ) + ''''
	end
	if @date_to is not null and ltrim(rtrim(@date_to)) <> ''  and  @date_to <> '0'
	begin
		set @sqlstring1 = @sqlstring1 + ' and MPDateOut <= ''' + convert(varchar, convert(datetime,@date_to) )  + ''''
	end


	set @sqlstring1 = @sqlstring1 + ' UNION '	
	--Finished Goods Information
	set @sqlstring1 = @sqlstring1 + N'
	SELECT LotNumber,BaseProduct,(select part_number from product where processstep_id = FG_Processstep_id) as PartNumber,
		null as Freight,null as Backgrind,
		null,null,		--Var for Fab
		null,null,		--Var for Sort
		null,null,		--Var for Die bank
		null,null,		--Var for Assembly
		null,null,		--Var for Test
		null,null,		--Var for Mark and Pack
		FGQtyOut,FGQtyOut*isnull( (select 
						case 
							when '+ convert(varchar,@QUARTER) + ' = 1 then Q1_STD_COST_PER_DIE 
							when '+ convert(varchar,@QUARTER) + ' = 2 then Q2_STD_COST_PER_DIE 
							when '+ convert(varchar,@QUARTER) + ' = 3 then Q3_STD_COST_PER_DIE 
							when '+ convert(varchar,@QUARTER) + ' = 4 then Q4_STD_COST_PER_DIE  
						end 
						from cost_table where processstep_id = FG_Processstep_id and location_id =FGLocation), 0.00),
		isnull(p_type,1)	-- for product_type 
	FROM Yield_Lot_Table where FGQtyIn > 0'

	if @baseproduct_id <> 0
	begin
		set @sqlstring1 = @sqlstring1 + ' and baseproduct_id ='+ convert(varchar,@baseproduct_id)
	end
	if @processstep_id <> 0
	begin
		set @sqlstring1 = @sqlstring1 + ' and FG_Processstep_id = '+ convert(varchar,@processstep_id)
	end
	if @date_from is not null and ltrim(rtrim(@date_from)) <> ''  and  @date_from <> '0'
	begin
		set @sqlstring1 = @sqlstring1 + ' and FGDateOut >=  ''' + convert(varchar, convert(datetime,@date_from) ) + ''''
	end
	if @date_to is not null and ltrim(rtrim(@date_to)) <> ''  and  @date_to <> '0'
	begin
		set @sqlstring1 = @sqlstring1 + ' and FGDateOut <= ''' + convert(varchar, convert(datetime,@date_to) )  + ''''
	end
	set @sqlstring1 = @sqlstring1 + ' UNION '	
	--Finished Goods Information
	--il_date_in,il_date_out,il_qty_in,il_qty_out

	set @sqlstring2 =  N'
	 	select	lt.lot_number,(select part_number from product where product_id = 
									case 
										when p.p_parent_product_id = -1 then p.product_id 
										when p.p_parent_product_id <> -1 then p.p_parent_product_id	
									end 
			      	 )as baseproduct,p.part_number,
				null as Freight,null as Backgrind,
				null,null,		--Var for Fab
				null,null,		--Var for Sort
				null,null,		--Var for Die bank
				null,null,		--Var for Assembly
				null,null,		--Var for Test
				null,null,		--Var for Mark and Pack
				il_qty_out,il_qty_out * isnull( (select 
								case 
									when '+ convert(varchar,@QUARTER) + ' = 1 then Q1_STD_COST_PER_DIE 
									when '+ convert(varchar,@QUARTER) + ' = 2 then Q2_STD_COST_PER_DIE 
									when '+ convert(varchar,@QUARTER) + ' = 3 then Q3_STD_COST_PER_DIE 
									when '+ convert(varchar,@QUARTER) + ' = 4 then Q4_STD_COST_PER_DIE 
								end 
								from cost_table where processstep_id = lt.processstep_id and location_id =lt.location_id), 0.00),
				case 
					when p_type is null then 1
					when p_type like ltrim(rtrim(''board'')) then 2
					when p_type like ltrim(rtrim(''kit'')) then 3
				end as p_type	-- for product_type 
	 from lot_table lt,product p,process_step ps,inventory_lot_history ilh 
	 where lt.processstep_id = p.processstep_id and p.processstep_id = ps.processstep_id and lt.lot_id = ilh.lot_id 
	 and lot_current_status = ''Close'' 
	 and ps_type_name = ''finished goods'' and (p_type = ''board'' or p_type = ''kit'') '

	if @baseproduct_id <> 0
	begin
		set @sqlstring2 = @sqlstring2 +' and  ( case 
							when p.p_parent_product_id = -1 then p.product_id
							when p.p_parent_product_id <> -1 then p.p_parent_product_id
						        end ) = '+ convert(varchar,@baseproduct_id) 
	end
	if @processstep_id <> 0
	begin
		set @sqlstring2 = @sqlstring2 + ' and  lt.processstep_id = '+ convert(varchar,@processstep_id)
	end
	if @date_from is not null and ltrim(rtrim(@date_from)) <> ''  and  @date_from <> '0'
	begin
		set @sqlstring2 = @sqlstring2 + ' and il_date_out >=  ''' + convert(varchar, convert(datetime,@date_from) ) + ''''
	end
	if @date_to is not null and ltrim(rtrim(@date_to)) <> ''  and  @date_to <> '0'
	begin
		set @sqlstring2 = @sqlstring2 + ' and il_date_out <= ''' + convert(varchar, convert(datetime,@date_to) )  + ''''
	end

	--PRINT @SQLSTRING
	--PRINT @SQLSTRING1
	--print @sqlstring2

	EXEC(@SQLSTRING+@sqlstring1+@sqlstring2)
		
	--Displaying record by lot wise when detail option is selected.
	if @category_type = 'Detail'
	begin
		select	
			LotNumber	,BaseProduct ,part_number, isnull(sum( Freight ), 0.00) as Freight, isnull(sum( Backgrind ), 0.00) as Backgrind,
			sum(FabQtyOut) as FabQtyOut, 		sum(FabCost) as FabCost, 
			sum(SortQtyOut) as SortQtyOut, 	sum(SortCost) as SortCost,
			sum(DiebankQtyOut) as DiebankQtyOut,	sum(DiebankCost) as DiebankCost,
			sum(AssmQtyOut) as AssmQtyOut, 	sum(AssmCost) as AssmCost,
			sum(TestQtyOut) as TestQtyOut, 	sum(TestCost) as TestCost,
			sum(MPQtyOut) as MPQtyOut, 		sum(MPCost) as MPCost,
			sum(FGQtyOut) as FGQtyOut, 		sum(FGCost) as FGCost,p_type  
		from #results
		group by p_type,LotNumber,BaseProduct,part_number 
	end
	--Displayin records by partnumber wise and calculating the weighted cost when summary option is selected
	else if @category_type = 'Summary'
	begin
		select	
			BaseProduct ,part_number, isnull(sum( Freight ), 0.00) as Freight,isnull(sum( Backgrind ), 0.00) as Backgrind,
			sum(FabQtyOut) as FabQtyOut, 		sum(FabQtyOut) * (sum(FabCost)/sum(FabQtyOut) )as FabCost, 
			sum(SortQtyOut) as SortQtyOut, 	sum(SortQtyOut) * (sum(SortCost)/sum(SortQtyOut) )as SortCost,
			sum(DiebankQtyOut) as DiebankQtyOut,	sum(DiebankQtyOut) * (sum(DiebankCost)/sum(DiebankQtyOut) )as DiebankCost,
			sum(AssmQtyOut) as AssmQtyOut, 	sum(AssmQtyOut) * (sum(AssmCost)/sum(AssmQtyOut) )as AssmCost,
			sum(TestQtyOut) as TestQtyOut, 	sum(TestQtyOut) * (sum(TestCost)/sum(TestQtyOut) )as TestCost,
			sum(MPQtyOut) as MPQtyOut, 		sum(MPQtyOut) * (sum(MPCost)/sum(MPQtyOut) )as MPCost,
			sum(FGQtyOut) as FGQtyOut, 		sum(FGQtyOut) * (sum(FGCost)/sum(FGQtyOut) )as FGCost,p_type
		from #results
		group by p_type,BaseProduct ,part_number 
	end
--select * from #results
delete  from #results
end

